/**
 * sqlite 服务
 */
const config = require('../config');
const logger = require('./logger');
let db = null
const tables = ['reviews']
// 初始化数据库连接
config.db.connectDB().then( () => {
  db = config.db.getDb()
  for (let table of tables) {
    if (!checkTableExits(table)) {
      if (table === "reviews") {
        createReviewsTable();
      }
    }
  }
});

// 检查相关的表是否已经创建
function checkTableExits(tableName){
  const checkTableQuery = `
    SELECT name FROM sqlite_master WHERE type='table' AND name='${tableName}';
  `;

  db.get(checkTableQuery, (err, row) => {
    if (err) {
      logger.error(err.message);
      return false
    } 
    if (row) {
      return true;
    } else {
      return false;
    }
  });
}

// 创建 reviews 表
function createReviewsTable() {
  const createReviewsTableSql = `
    CREATE TABLE IF NOT EXISTS reviews (    
    questionId string,
    mode string,       
    question string,       
    answer string,       
    score REAL,       
    expression string,      
    grammar string,      
    createdAt string,
    updatedAt string     
);  `;
  db.run(createReviewsTableSql, (err) => {
    if (err) {
      logger.error(err.message);
      return false
    } else {
      logger.info("reviews table created successfully");
      return true
    }
  });
}
// 辅助函数：生成指定范围内的随机数
function getRandomNumber(min, max, decimals = 1) {
  const random = Math.random() * (max - min) + min;
  return Number(random.toFixed(decimals));
}
// 保存评测结果
function saveReview(data){
  let sql = `INSERT INTO reviews (questionId, mode,question, answer, score, expression,grammar, createdAt, updatedAt) VALUES (?,?, ?, ?, ?, ?, ?, ?,?)`;
  const {
    questionId,
    mode,
    question,
    answer,
    score,
    expression,
    grammar,
    createdAt,
    updatedAt,
  } = data;
  db.run(
    sql,
    [
      questionId,
      mode,
      question,
      answer,
      score,
      expression,
      grammar,
      createdAt,
      updatedAt,
    ],
    function (err) {
      if (err) {
        logger.error("保存评测结果失败:", err.message);
      }
    }
  );
}

function fetchReviews(){
  let sql = `SELECT * FROM reviews order by createdAt desc limit 5`;
  return new Promise((resolve, reject) => {
      try {
        db.all(sql, [], (err, rows) => {
          if (err) {
            logger.error("获取评测结果失败:", err.message);
            reject(err);
          } else {
            logger.info("获取评测结果成功:", rows);
            resolve(rows);
          }
        });
      } catch(error) {
        reject(error)
      }
  })
}

// 获取评测条数
function fetchCount() {
  return  new Promise((resolve, reject) => {
    try {
      const sql = `SELECT COUNT(*) as count FROM reviews`;
      db.get(sql, [], (err, row) => {
        if (err) {
          logger.error("获取评测结果失败:", err.message);
          reject(err);
        } else {
          logger.info("获取评测结果成功:", row);
          resolve(row);
        }
      });
    } catch (error) {
      reject(err)
    };
  })
}
function fetchModeStat() {
  return new Promise((resolve, reject) => {
    try {
      const sql = `SELECT mode, score,createdAt FROM (SELECT * FROM reviews ORDER BY createdAt DESC)  GROUP BY mode;`;
      db.all(sql, [], (err, rows) => {
        if (err) {
          logger.error("获取评测结果失败:", err.message);
          reject(err);
        } else {
          logger.info("获取评测结果成功:", rows);
          resolve(rows);
        }
      });
    } catch (error) {
      reject(err);
    }
  });
}
module.exports = {
  saveReview,
  fetchReviews,
  fetchCount,
  fetchModeStat,
};